package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import javax.naming.NamingException;

import com.crm.customer.model.Customer;

public class CustomersDAO extends CustomerBaseDAO {
	public List<Customer> getAllCustomers() {
		List<Customer> e = new LinkedList<Customer>();
		ResultSet rs = null;
		Connection conn = null;
		try {
			conn = getConnection();
			Statement statement = conn.createStatement();
			String query = "SELECT * FROM customers";
			rs = statement.executeQuery(query);
			while (rs.next()) {
				String customer_id = rs.getString("customer_id");
				String firstName = rs.getString("firstName");
				String lastName = rs.getString("lastName");
				String sex = rs.getString("sex");
				String package_id = rs.getString("package_id");
				String employee_id = rs.getString("employee_id");
				int age = rs.getInt("age");
				int height = rs.getInt("height");
				int weight = rs.getInt("weight");
				
				Customer customer = new Customer(customer_id, firstName,
						lastName, sex, package_id, employee_id, age, height, weight);
				e.add(customer);
				
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return e; 
	}
	
	public boolean addCustomer(Customer customer) {
		boolean result = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			 conn = getConnection();
			 String sql = "INSERT INTO customers(CUSTOMER_ID, FIRSTNAME, LASTNAME, SEX, PACKAGE_ID, EMPLOYEE_ID, AGE, HEIGHT,WEIGHT) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
			    ps = conn.prepareStatement(sql);
				ps.setString(1, customer.getCustomer_id());
				ps.setString(2, customer.getFirstName());
				ps.setString(3, customer.getLastName());
				ps.setString(4, customer.getSex());
				ps.setString(5, customer.getPackage_id());
				ps.setString(6, customer.getEmployee_id());
				ps.setInt(7, customer.getAge());
				ps.setInt(8, customer.getHeight());
				ps.setInt(9, customer.getWeight());
				ps.execute();
				result = true; 
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	return result;
	
    }
	
	public boolean deleteCustomer(String customer_id) {
		boolean result = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			 conn = getConnection();
			 String sql = "DELETE FROM customers WHERE CUSTOMER_ID = ?";
			    ps = conn.prepareStatement(sql);
				ps.setString(1, customer_id);
				ps.execute();
				result = true; 
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	return result;
	
    }
	public Customer findSingleCustomer(String customer_id) {
		Customer customer = null;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			 conn = getConnection();
			 String sql = "SELECT* FROM customers WHERE CUSTOMER_ID = ?";
			    ps = conn.prepareStatement(sql);
				ps.setString(1, customer_id);
				
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				customer = new Customer(rs.getString("CUSTOMER_ID"),rs.getString("FIRSTNAME"),rs.getString("LASTNAME"),rs.getString("SEX"),rs.getString("PACKAGE_ID"),rs.getString("EMPLOYEE_ID"),rs.getInt("AGE"),rs.getInt("HEIGHT"),rs.getInt("WEIGHT"));
			}
				
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return customer;


	
    }
	
	public boolean editCustomer(Customer customer) {
		boolean result = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			 conn = getConnection();
			 String sql = "UPDATE customers SET FIRSTNAME = ?,LASTNAME = ?,SEX = ?,PACKAGE_ID = ?,EMPLOYEE_ID = ?,AGE = ?,HEIGHT = ?,WEIGHT = ?";
			    ps = conn.prepareStatement(sql);
			    ps.setString(2, customer.getFirstName());
				ps.setString(3, customer.getLastName());
				ps.setString(4, customer.getSex());
				ps.setString(5, customer.getPackage_id());
				ps.setString(6, customer.getEmployee_id());
				ps.setInt(7, customer.getAge());
				ps.setInt(8, customer.getHeight());
				ps.setInt(9, customer.getWeight());
				ps.execute();
				result = true; 
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	return result;
	
    }
}
